import com.shiyi.compute;
import javafx.scene.control.TableSelectionModel;

import java.sql.*;
import java.util.LinkedHashMap;
import java.util.Map;

public class test2 {
    public static final String url = "jdbc:mysql://rm-uf6v59d4fuzneh9mpko.mysql.rds.aliyuncs.com:3306/gbtest";
    public static final String user = "shiyidata";
    public static final String passWord = "Bestage1116";
    public static final String table = "xf_package";
//    public static final String table = "xf_test";
//    public static final String table = "test";
    public static final String endtable = "xf_pack_end10";
//    public static final String endtable = "xf_package1";
        public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
            //加载mysql驱动
//        Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接
            Connection connection = DriverManager.getConnection(url, user, passWord);
            connection.setAutoCommit(false);
//        PreparedStatement preparedStatement = connection.prepareStatement("select  * from order_test")

            /*
             * 前置操作
             *   1.顺丰表清除
             *   2.顺丰表过滤字段并插入
             *   3.包裹表过滤和插入数据
             *   4.更新包裹表不可见字符
             *   xf_package清除
             *   5.xf_package中插入数据
             *   6.将包裹标准重量过大的带有都好的更改
             *   7.清理最终表数据
             * */

            //1 顺丰表清除
            PreparedStatement truncate_table_xf_foundation1 = connection.prepareStatement("truncate table xf_foundation1");
            truncate_table_xf_foundation1.execute();
            truncate_table_xf_foundation1.close();

            //2.顺丰表过滤字段并插入
            PreparedStatement pxf = connection.prepareStatement("insert into xf_foundation1 select 序号,日期,运单号码,寄件地区,到件地区,对方公司名称,实际重量,计费重量,产品类型," +
                    "       付款方式,`费用(元)`,`折扣/促销`,应付金额,经手人,增值费用,`始发地(省名)`,`目的地(省名)`" +
                    "from xf_foundation");
            pxf.execute();
            pxf.close();

            //清除package_simple
            PreparedStatement truncate_table_package_simple = connection.prepareStatement("truncate table package_simple");
            truncate_table_package_simple.execute();
            truncate_table_package_simple.close();

            //3.包裹表过滤和插入数据
            PreparedStatement ppack = connection.prepareStatement("insert into package_simple select 物流单号,物流公司,称重重量,标准重量 " +
                    "from package_details_test");
            ppack.execute();
            ppack.close();

            //4.更新包裹表不可见字符
            PreparedStatement pupset = connection.prepareStatement("update package_simple set 物流单号 = replace(物流单号,'\t','')");
            pupset.execute();
            pupset.close();

            //xf_package清除
            PreparedStatement truncate_table_xf_package = connection.prepareStatement("truncate table xf_package");
            truncate_table_xf_package.execute();
            truncate_table_xf_package.close();

            //5.xf_package中插入数据
            PreparedStatement pxfinsert = connection.prepareStatement("insert into xf_package select * from xf_foundation1" +
                    " join package_simple " +
                    " where 运单号码 = 物流单号");
            pxfinsert.execute();
            pxfinsert.close();
            System.out.println("--------------------------------------");
            //6.将包裹标准重量过大的带有都好的更改
            PreparedStatement pupxfset = connection.prepareStatement("update xf_package set 包裹标准重量 = replace(包裹标准重量,',','')");
            pupxfset.execute();
            pupxfset.close();

            //System.out.println(preparedStatement.getFetchSize());
            //获取日期 次数
            Map<String, Integer> map = new LinkedHashMap<String, Integer>();
            PreparedStatement pcount = connection.prepareStatement("select  substr(日期,1,2) 日期,count(*) 次数 from " +table + " group by substr(日期,1,2) ");
            ResultSet count = pcount.executeQuery();
            while (count.next()){
                String data = count.getString("日期");
                int counts =Integer.parseInt(count.getString("次数")) /30;
                System.out.println(Integer.parseInt(count.getString("次数")));
                map.put(data,counts);
            }
            pcount.close();
            count.close();

//            7.清理最终表数据
            PreparedStatement truncate = connection.prepareStatement("truncate table " + endtable );
            truncate.execute();
            truncate.close();

            /**
             * 创建插入表
             */
            PreparedStatement create = connection.prepareStatement("create table if not exists " + endtable+"(" +
                    "  `序号` varchar(255) ," +
                    "  `日期` varchar(255) ," +
                    "  `运单号码` varchar(255) ," +
                    "  `寄件地区` varchar(255)," +
                    "  `到件地区` varchar(255) ," +
//                    "  `对方公司名称` varchar(255) ," +
//                    "  `实际重量` varchar(255) ," +     //不要
                    "  `计费重量` varchar(255) ," +
                    "  `产品类型` varchar(255) ," +
//                    "  `付款方式` varchar(255) ," +     //不要
                    "  `费用(元)` varchar(255) ," +
                    "  `折扣/促销` varchar(255) ," +
                    "  `应付金额` varchar(255)," +
//                    "  `经手人` varchar(255)," +       //不要
                    "  `增值费用` varchar(255)," +
                    "  `始发地(省名)` varchar(255) ," +
                    "  `目的地(省名)` varchar(255) ," +
//                    "  `包裹物流单号` varchar(255)," +
//                    "  `包裹物流公司` varchar(255)," +
                    "  `包裹称重重量` varchar(255)," +
                    "  `包裹标准重量` varchar(255)," +
                     "  `核对计费重量` varchar(255)," +
                    "  `重量差` DECIMAL(9,3)," +
                    "    `计算运费` DECIMAL(9,3)," +
                    "    `差异金额` DECIMAL(9,3)," +
                    "  `备注` varchar(255)"+
                    ") ");
            create.execute();
            create.close();

            PreparedStatement pinsert = null;
            pinsert = connection.prepareStatement("insert into "+endtable+" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            int i = 0;
            //便利日期
            for(String key:map.keySet()) {
//          System.out.println(key+ ":" + map.get(key));

                //获得以天为单位的数据
                PreparedStatement pdata = connection.prepareStatement("select * from " + table+" where substr(日期,1,2) = '" + key + "'");
//                PreparedStatement pdata = connection.prepareStatement("select * from " + table+" where 日期 = '09-01' ");
                ResultSet rdata = pdata.executeQuery();
//                System.out.println(key);
                //遍历每天的数据
                while (rdata.next()){

                    //获取每行数据
                    //序号hao  始发地(省名)origin_address  目的地(省名) destination 包裹称重重量weight  包裹标准重量standard_weight 计费重量bill_weight
                    //删除表

                    /**
                     * 计算逻辑
                     */
                    String hao = rdata.getString("序号");
                    String data = rdata.getString("日期");
                    String freight = rdata.getString("运单号码");
                    String adress = rdata.getString("寄件地区");
                    String endadress = rdata.getString("到件地区");
                    String company = rdata.getString("对方公司名称");
                    String weight_true= rdata.getString("实际重量");
                    String bill_weight = rdata.getString("计费重量");
                    String product_type = rdata.getString("产品类型");
                    String payment_method = rdata.getString("付款方式");
                    String cost = rdata.getString("费用(元)");
                    String reduce = rdata.getString("折扣/促销");
                    String cost_true = rdata.getString("应付金额");
                    String person = rdata.getString("经手人");
                    String cost_add = rdata.getString("增值费用");
                    String origin_address = rdata.getString("始发地(省名)");
                    String destination = rdata.getString("目的地(省名)");
                    String order_num = rdata.getString("包裹物流单号");
                    String company_logistics = rdata.getString("包裹物流公司");
                    Integer num = map.get(key);//次数
                    String weight = rdata.getString("包裹称重重量");
                    String standard_weight = rdata.getString("包裹标准重量");
//                    System.out.println(hao+ "     ");
                    if (null==cost_add )
                    {
//                        System.out.println(cost_add);
                        String ss = "";
                        double cost_weight = compute.compute_weight(hao, weight, standard_weight, bill_weight);
                        double v = compute.compute_price( hao ,product_type,origin_address,endadress, destination, cost_true,num, cost_weight);
                        double vv=Double.parseDouble(cost_true)-v;
                        if (vv <0){
                            vv = 0.00;
                        }
                        pinsert.setString(1,hao);
                        pinsert.setString(2,data);
                        pinsert.setString(3,freight);
                        pinsert.setString(4,adress);
                        pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                        pinsert.setString(6,bill_weight);
                        pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                        pinsert.setString(8,cost);
                        pinsert.setString(9,reduce);
                        pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                        pinsert.setString(11,cost_add);
                        pinsert.setString(12,origin_address);
                        pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                        pinsert.setString(14,weight);
                        pinsert.setString(15,standard_weight);
                        pinsert.setDouble(16,cost_weight);
                        String weight_diff = Math.abs(cost_weight - Double.parseDouble(bill_weight))+"";
                        pinsert.setString(17,weight_diff);
                        pinsert.setDouble(18,v);
                        pinsert.setDouble(19,vv);
                        if (Double.parseDouble(weight_diff) > 0 ) ss+="重量有差异 ";
                        if ("顺丰标快".equals(product_type) &&  (!"西藏".equals(destination)) && !"玉树藏族".equals(endadress)) ss +="为什么是顺丰标快 ";
                        if ("顺丰特快".equals(product_type) && !"西藏".equals(destination)) ss +="为什么是顺丰特快 ";
                        pinsert.setString(20,ss);
                        pinsert.addBatch();
                        i++;
                    }
                    else if ( "同城退回".equals(cost_add)){
                        String ss = "";
                        double cost_weight = compute.compute_weight(hao, weight, standard_weight, bill_weight);
                        double v = compute.compute_price( hao ,product_type,origin_address,endadress, destination, cost_true,2500, cost_weight);
                        double vv=Double.parseDouble(cost_true)-v;
                        if (vv <0){
                            vv = 0.00;
                        }
                        pinsert.setString(1,hao);
                        pinsert.setString(2,data);
                        pinsert.setString(3,freight);
                        pinsert.setString(4,adress);
                        pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                        pinsert.setString(6,bill_weight);
                        pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                        pinsert.setString(8,cost);
                        pinsert.setString(9,reduce);
                        pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                        pinsert.setString(11,cost_add);
                        pinsert.setString(12,origin_address);
                        pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                        pinsert.setString(14,weight);
                        pinsert.setString(15,standard_weight);
                        pinsert.setDouble(16,cost_weight);
                        String weight_diff = Math.abs(cost_weight - Double.parseDouble(bill_weight))+"";
                        pinsert.setString(17,weight_diff);
                        pinsert.setDouble(18,v);
                        pinsert.setDouble(19,vv);
                        if (Double.parseDouble(weight_diff) > 0 ) ss+="重量有差异 ";
                        if ("顺丰标快".equals(product_type) &&  (!"西藏".equals(destination)) && !"玉树藏族".equals(endadress)) ss +="为什么是顺丰标快 ";
                        if ("顺丰特快".equals(product_type) && !"西藏".equals(destination)) ss +="为什么是顺丰特快 ";
                        pinsert.setString(20,ss);
                        pinsert.addBatch();
                        i++;
                    }else if ("异地退回".equals(cost_add)){
                        String ss = "";
                        double cost_weight = compute.compute_weight(hao, weight, standard_weight, bill_weight);
                        double v = compute.compute_price( hao ,product_type,destination,endadress,origin_address , cost,2500, cost_weight);
                        double vv=Double.parseDouble(cost)-v;
                        if (vv <0){
                            vv = 0.00;
                        }
                        pinsert.setString(1,hao);
                        pinsert.setString(2,data);
                        pinsert.setString(3,freight);
                        pinsert.setString(4,adress);
                        pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                        pinsert.setString(6,bill_weight);
                        pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                        pinsert.setString(8,cost);
                        pinsert.setString(9,reduce);
                        pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                        pinsert.setString(11,cost_add);
                        pinsert.setString(12,origin_address);
                        pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                        pinsert.setString(14,weight);
                        pinsert.setString(15,standard_weight);
                        pinsert.setDouble(16,cost_weight);
                        String weight_diff = Math.abs(cost_weight - Double.parseDouble(bill_weight))+"";
                        pinsert.setString(17,weight_diff);
                        pinsert.setDouble(18,v);
                        pinsert.setDouble(19,vv);
                        if (Double.parseDouble(weight_diff) > 0 ) ss+="重量有差异 ";
                        if ("顺丰标快".equals(product_type) && !"西藏".equals(origin_address)) ss +="为什么是顺丰标快 ";
                        if ("顺丰特快".equals(product_type) && !"西藏".equals(origin_address)) ss +="为什么是顺丰标快 ";
                        pinsert.setString(20,ss);
                        pinsert.addBatch();
                        i++;
                    }
                    else {
                        pinsert.setString(1,hao);
                        pinsert.setString(2,data);
                        pinsert.setString(3,freight);
                        pinsert.setString(4,adress);
                        pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                        pinsert.setString(6,bill_weight);
                        pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                        pinsert.setString(8,cost);
                        pinsert.setString(9,reduce);
                        pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                        pinsert.setString(11,cost_add);
                        pinsert.setString(12,origin_address);
                        pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                        pinsert.setString(14,weight);
                        pinsert.setString(15,standard_weight);
                        pinsert.setDouble(16,Double.parseDouble(bill_weight));
                        pinsert.setString(17,"0");
                        pinsert.setDouble(18,Double.parseDouble(cost_true) );
                        pinsert.setDouble(19,0.00);
                        pinsert.setString(20," ");
                        pinsert.addBatch();
                        i++;

                    }

                    while (i>3000)
                    {
                        pinsert.executeBatch();
                        i = 0;
                        connection.commit();
                    }

                    /**
                     * 插入数据
                     */

//                    System.out.println( hao+" " + key + " " + origin_address+" "+" "+destination + " "+
//                            num + " " + weight + " " + standard_weight + " " + bill_weight +" " +
//                            compute.compute_price(origin_address, destination, num, compute.compute_weight(weight, standard_weight, bill_weight)));

//                    System.out.println("-----------------------------------------");
                }
                pinsert.executeBatch();
                connection.commit();
            }
            pinsert.close();
        }
}
